﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Web.Configuration;
using Jungle.Code.Core;

namespace Jungle.Code.DAL
{
    public class DataInterface
    {

        private static string connectionString = WebConfigurationManager.ConnectionStrings["dbConnection"].ToString();
        private static SqlConnection connection = null;

        #region Public methods
        public static DataTable FillDataTable(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            Connect();
            cmd.Connection = connection;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            Debugger.AddMessage(cmd.CommandText, MessageType.SqlQuery);
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка при зверненні до бази даних: " + ex.Message);
            }
            return dt;
        }
        public static int ExecuteNonQuery(SqlCommand cmd)
        {
            Connect();
            cmd.Connection = connection;
            Debugger.AddMessage(cmd.CommandText, MessageType.SqlQuery);

            int affected = 0;
            try
            {
                affected = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка виконання запиту: " + ex.Message);
            }
            return affected;
        }
        public static object ExecuteScalar(SqlCommand cmd)
        {
            Connect();
            Debugger.AddMessage(cmd.CommandText, MessageType.SqlQuery);

            cmd.Connection = connection;
            object obj = null;
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка виконання запиту: " + ex.Message);
            }
            return obj;

        }

        public static DataTable FillDataTable(string sql)
        {
            DataTable dt = new DataTable();
            Connect();
            SqlCommand cmd = new SqlCommand(sql);
            Debugger.AddMessage(sql, MessageType.SqlQuery);

            cmd.Connection = connection;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка при зверненні до бази даних: " + ex.Message);
            }
            return dt;
        }
        public static int ExecuteNonQuery(string sql)
        {
            Connect();
            SqlCommand cmd = new SqlCommand(sql);
            Debugger.AddMessage(sql, MessageType.SqlQuery);

            cmd.Connection = connection;
            int affected = 0;
            try
            {
                affected = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка виконання запиту: " + ex.Message);
            }
            return affected;
        }
        public static object ExecuteScalar(string sql)
        {
            Connect();
            SqlCommand cmd = new SqlCommand(sql); 
            Debugger.AddMessage(sql, MessageType.SqlQuery);

            cmd.Connection = connection;
            object obj = null;
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка виконання запиту: " + ex.Message);
            }
            return obj;

        }

        public static int LastInsertedID()
        {
            Connect();
            SqlCommand cmd = new SqlCommand("SELECT @@IDENTITY");
            cmd.Connection = connection;
            try
            {
                object obj = cmd.ExecuteScalar();
                return int.Parse(obj.ToString());
            }
            catch (Exception ex)
            {
                connection.Close();
                throw new Exception("Помилка виконання запиту: " + ex.Message);
            }
        }
        #endregion

        #region Private methods
        private static void Connect()
        {
            if (connection == null)
            {
                connection = new SqlConnection(connectionString);
            }

            if (connection.State == ConnectionState.Closed)
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    connection.Close();
                    throw new Exception("Помилка при зверненні до бази даних: " + ex.Message);
                }
        }
        #endregion


    }
}
